package com.epoint.bookmanagement.dao;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.epoint.bookmanagement.bizlogic.bookinfo.domain.BookAndBorrowInfoVo;
import com.epoint.bookmanagement.bizlogic.bookinfo.domain.BookInfo;
import com.epoint.bookmanagement.utils.JDBCUtil;
import com.epoint.bookmanagement.utils.SQLUtil;

import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;

/**
 * 图书信息
 * 
 * @author
 *
 */
public class BookInfoDao {

	/**
	 * 新增图书信息
	 * 
	 * @param bookInfo 图书信息
	 * @return 结果
	 */
	public int addBookInfo(BookInfo bookInfo) {
		Connection con = JDBCUtil.getConnection();
		String sql = "insert into bookinfo (bookid, bookname, publisher, author, booktype, remain) values (?,?,?,?,?,?)";
		PreparedStatement ps = null;
		int i = 0;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, bookInfo.getBookId());
			ps.setString(2, bookInfo.getBookName());
			ps.setString(3, bookInfo.getPublisher());
			ps.setString(4, bookInfo.getAuthor());
			ps.setInt(5, bookInfo.getBookType());
			ps.setInt(6, bookInfo.getRemain());
			System.err.println("addBookInfo -->sql-->" + ps);
			i = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(null, ps, con);
		}
		return i;
	}

	/**
	 * 隔开的多个id，实现单表的多选删除
	 * 
	 * @param bookIds id集合
	 * @return 结果
	 */
	public int deleteBookinfo(String bookIds) {
		Connection con = JDBCUtil.getConnection();
		String sql = "delete from bookinfo where find_in_set (bookid,?)";

		PreparedStatement ps = null;
		int i = 0;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, bookIds);
			System.err.println("deleteBookinfo -->sql-->" + ps);
			i = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(null, ps, con);
		}
		return i;
	}

	/**
	 * 更新操作
	 * 
	 * @param bookinfo 书信息
	 * @return 条数
	 */
	public int updateBookinfo(BookInfo bookInfo) {
		Connection con = JDBCUtil.getConnection();
		String sql = "update bookinfo set bookname = ?, publisher = ?, author = ?, booktype = ?, remain = ? where bookid = ?";
		PreparedStatement ps = null;
		int i = 0;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, bookInfo.getBookName());
			ps.setString(2, bookInfo.getPublisher());
			ps.setString(3, bookInfo.getAuthor());
			ps.setInt(4, bookInfo.getBookType());
			ps.setInt(5, bookInfo.getRemain());
			ps.setString(6, bookInfo.getBookId());
			System.err.println("dpdateBookinfo -->sql-->" + ps);
			i = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(null, ps, con);
		}
		return i;
	}

	/**
	 * 根据id查询图书信息
	 * 
	 * @param bookId id
	 * @return 结果
	 */
	public BookInfo queryBookInfoByID(String bookId) {
		Connection con = JDBCUtil.getConnection();
		String sql = "select * from bookinfo where bookid = ?";
		PreparedStatement ps = null;
		ResultSet rs = null;

		BookInfo bookInfo = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, bookId);
			System.err.println("queryBookInfoByID -->sql-->" + ps);
			rs = ps.executeQuery();
			// 封装结果
			while (rs.next()) {
				bookInfo = new BookInfo();
				bookInfo.setBookId(rs.getString("bookid"));
				bookInfo.setBookName(rs.getString("bookname"));
				bookInfo.setAuthor(rs.getString("author"));
				bookInfo.setPublisher(rs.getString("publisher"));
				bookInfo.setBookType(rs.getInt("booktype"));
				bookInfo.setRemain(rs.getInt("remain"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(rs, ps, con);
		}
		return bookInfo;
	}

	/**
	 * 根据书名图书类型模糊查询图书信息
	 * 
	 * @param pageIndex 页号
	 * @param pageSize  页面大小
	 * @param bookname  书名
	 * @param booktype  类型
	 * @return
	 */
	public List<BookInfo> queryBookinfoByKeyWords(Integer pageIndex, Integer pageSize, String bookname, Integer booktype) {
		Connection con = JDBCUtil.getConnection();
		
		PreparedStatement ps = null;
		ResultSet rs = null;

		List<BookInfo> bookInfoList = new ArrayList<BookInfo>();
		BookInfo bookInfo = null;

		StringBuffer sql = new StringBuffer();
		sql.append(" select * from bookinfo where 1 =1 ");
		if (!StrUtil.isEmpty(bookname)) {
			sql.append(" and bookname like concat('%', ? ,'%') ");
		}
		if (booktype != null) {
			sql.append("and booktype = ? ");
		}
		// 选择性分页
		if (pageIndex != null && pageSize != null) {
			sql.append(" limit ?,? ");
		}
		
		int i = 1;
		try {
			ps = con.prepareStatement(sql.toString());
			if (!StrUtil.isEmpty(bookname)) {
				ps.setString(i, bookname);
				i++;
			}
			if (booktype != null) {
				ps.setInt(i, booktype);
				i++;
			}
			// 选择性分页
			if (pageIndex != null && pageSize != null) {
				int indexNum = pageIndex * pageSize;
				ps.setInt(i++, indexNum);
				ps.setInt(i++, pageSize);
			}
			System.err.println("queryBookinfoByKeyWords -->sql-->" + ps);
			// 封装查询结果
			rs = ps.executeQuery();
			while (rs.next()) {
				bookInfo = new BookInfo();
				bookInfo.setBookId(rs.getString("bookid"));
				bookInfo.setBookName(rs.getString("bookname"));
				bookInfo.setAuthor(rs.getString("author"));
				bookInfo.setPublisher(rs.getString("publisher"));
				bookInfo.setBookType(rs.getInt("booktype"));
				bookInfo.setRemain(rs.getInt("remain"));
				bookInfoList.add(bookInfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(rs, ps, con);
		}
		return bookInfoList;
	}

	/**
	 * 根据对象所有属性进行模糊查询
	 * 
	 * @param pageIndex 页号
	 * @param pageSize  页面大小
	 * @param bookInfo  书本信息
	 * @return 结果
	 */
	public List<BookInfo> queryBookinfoByBookInfo(Integer pageIndex, Integer pageSize, BookInfo bookInfo,
			String... columns) {
		List<Object> list = SQLUtil.reflectionSelecTableInfo(pageIndex, pageSize, bookInfo, "bookinfo",
				"com.epoint.bookmanagement.bizlogic.bookinfo.domain.BookInfo", columns);

		List<BookInfo> reslutList = new ArrayList<>(list.size());

		for (Object o : list) {

			reslutList.add((BookInfo) o);
		}
		return reslutList;
	}

	/**
	 * 判断排除现有的图书，检查其他所有图书中是否重名
	 * @param beforeBookName 数据库现有的
	 * @param nowBookName 如今修改的
	 * @param author 图书作者
	 * @return 结果
	 */
	public BookInfo checkExist(String beforeBookId, String nowBookName, String author) {
		Connection con = JDBCUtil.getConnection();
		String sql = "select * from bookinfo where bookid != ? and bookname = ? and author = ?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		BookInfo bookInfo = null;
		
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, beforeBookId);
			ps.setString(2, nowBookName);
			ps.setString(3, author);
			System.err.println("checkExist -->sql-->" + ps);
			rs = ps.executeQuery();
			
			while(rs.next()) {
				bookInfo = new BookInfo();
				bookInfo.setBookId(rs.getString("bookid"));
				bookInfo.setBookName(rs.getString("bookname"));
				bookInfo.setAuthor(rs.getString("author"));
				bookInfo.setPublisher(rs.getString("publisher"));
				bookInfo.setBookType(rs.getInt("booktype"));
				bookInfo.setRemain(rs.getInt("remain"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(rs, ps, con);
		}
		return bookInfo;
	}

	/**
	 * 库存减一
	 * @param bookid
	 * @return
	 */
	public int subBookinfoByRemain(String bookid) {
		Connection con = JDBCUtil.getConnection();
		String sql = "update bookinfo set remain = (remain - 1) where bookid = ?";
		PreparedStatement ps = null;
		int i = 0;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, bookid);
			System.err.println("checkExist -->sql-->" + ps);
			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(null, ps, con);
		}
		
		return i;
	}

	/**
	 * 根据bookid获取图书信息Map<bookid, BookInfo>
	 * @param bookIds id集合
	 * @return 结果
	 */
	public Map<String, BookInfo> queryBookInfoByIds(List<String> bookIds) {
		Connection con = JDBCUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		BookInfo bookInfo = null;
		Map<String, BookInfo> result = null;
		
		StringBuffer sql = new StringBuffer();
		sql.append(" select * from bookinfo where bookid in ( ");
		
		int size = bookIds.size();
		for (int i = 0; i < bookIds.size(); i++) {
			sql.append(" ? ");
			if (i < size - 1) {
				sql.append(" , ");
			}
		}
		sql.append(" ) ");
		

		try {
			ps = con.prepareStatement(sql.toString());
			for (int i = 0; i < bookIds.size(); i++) {
				ps.setString(i + 1, bookIds.get(i));
			}
			System.err.println("queryBookInfoByIds -->sql-->" + ps);
			// 封装查询结果
			rs = ps.executeQuery();
			result = new HashMap<String, BookInfo>(size);
			while (rs.next()) {
				bookInfo = new BookInfo();
				bookInfo.setBookId(rs.getString("bookid"));
				bookInfo.setBookName(rs.getString("bookname"));
				bookInfo.setAuthor(rs.getString("author"));
				bookInfo.setPublisher(rs.getString("publisher"));
				bookInfo.setBookType(rs.getInt("booktype"));
				bookInfo.setRemain(rs.getInt("remain"));
				result.put(rs.getString("bookid"), bookInfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(rs, ps, con);
		}
		return result;
	}

	/**
	 * 根据bookid查询图书的借阅相关信息
	 * @param bookIds 图书id
	 * @return 结果
	 */
	public List<BookAndBorrowInfoVo> selectBookAndBorrowInfoByBookId(String bookIds) {
		
		Connection con = JDBCUtil.getConnection();
		String sql = "select bo.bookid as bookId, bo.bookname as bookName, br.borrowid as borrowId, br.returntime as returnTime "
				+ "from bookinfo bo left join borrowinfo br on bo.bookid = br.bookid where find_in_set (bo.bookid,?)";
		PreparedStatement ps = null;
		ResultSet rs = null;
		BookAndBorrowInfoVo vo = null;
		List<BookAndBorrowInfoVo> result = new ArrayList<>();
		
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, bookIds);
			System.err.println("selectBookAndBorrowInfoByBookId -->sql-->" + ps);
			rs = ps.executeQuery();
			
			while(rs.next()) {
				vo = new BookAndBorrowInfoVo();
				vo.setBookId(rs.getString("bookId"));
				vo.setBookName(rs.getString("bookName"));
				vo.setBorrowId(rs.getString("borrowId"));
				vo.setReturnTime(rs.getTimestamp("returnTime"));
				result.add(vo);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(rs, ps, con);
		}
		return result;
	}

	/**
	 * 删除图书和相关的借阅信息
	 * @param delBookIds
	 * @return
	 */
	public int deleteBookAndBorrowInfo(String delBookIds) {
		Connection con  = JDBCUtil.getConnection();
		String sql1 = "select count(1) from bookinfo where find_in_set (bookid,?)";
		String sql2 = "select count(1) from borrowinfo where find_in_set (bookid,?)";
		String sql3 = "delete from bookinfo where find_in_set (bookid,?)";
		String sql4 = "delete from borrowinfo where find_in_set (bookid,?)";
		PreparedStatement ps = null;
		ResultSet rs = null;
		int result = 0, i = 0, j = 0;
		try {
			ps = con.prepareStatement(sql1);
			ps.setString(1, delBookIds);
			System.err.println("deleteBookAndBorrowInfo-->sql1-->" + ps);
			rs = ps.executeQuery();
			int total1 = 0, total2 = 0;
			while(rs.next()) {
				total1 = rs.getInt(1);
			}
			rs.close();
			ps.close();
			// 取消自动提交事务
			con.setAutoCommit(false);
			ps = con.prepareStatement(sql3);
			ps.setString(1, delBookIds);
			System.err.println("deleteBookAndBorrowInfo-->sql3-->" + ps);
			i = ps.executeUpdate();
			if (i == total1) {
				rs.close();
				ps.close();
				ps = con.prepareStatement(sql2);
				ps.setString(1, delBookIds);
				System.err.println("deleteBookAndBorrowInfo-->sql2-->" + ps);
				rs = ps.executeQuery();
				while(rs.next()) {
					total2 = rs.getInt(1);
				}
				rs.close();
				ps.close();
				ps = con.prepareStatement(sql4);
				ps.setString(1, delBookIds);
				System.err.println("deleteBookAndBorrowInfo-->sql4-->" + ps);
				j = ps.executeUpdate();
			}
			// 判读事务提交
			if (i == total1 && j == total2 ) {
				result = 1;
				// 手动提交事务
				con.commit();
			} else {
				// 事务回滚
				con.rollback(); 
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(rs, ps, con);
		}
		return result;
	}

}
